﻿<%@ WebHandler Language="C#" Class="MESMonthDataReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: MES系统应用情况月报报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.25
 **/

public class MESMonthDataReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    DataTable GetAllFactory(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,ua.attributevalue 
FROM factory f
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL ";
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }
        return dal.GetDataTable(sql);
    }

    DataTable getResultData(JObject jObject)
    {
        //按分厂和月份查询派工记录
        string sql = @"
SELECT f.factoryname,count(csd.containerstepdispatchid) dispatchQty
FROM containerstepdispatch csd
LEFT JOIN factory f ON f.factoryid = csd.factoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND csd.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND csd.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtDispatchQty = dal.GetDataTable(sql);

        //按分厂和月份查询报工记录
        sql = @"
SELECT f.factoryname,count(csr.containerstepworkreportid) reportQty
FROM containerstepworkreport csr
LEFT JOIN factory f ON f.factoryid = csr.factoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL ";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND csr.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND csr.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtReportQty = dal.GetDataTable(sql);

        //按分厂和月份查询检验记录（工序检验+成品检验）
        //查工序检验
        sql = @"
SELECT f.factoryname,count(cci.containercheckinfoid) spcecheckQty
FROM containercheckinfo cci
LEFT JOIN factory f ON f.factoryid = cci.factoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL ";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cci.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cci.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtSpecCheckQty = dal.GetDataTable(sql);

        //成品检验
        sql = @"
SELECT f.factoryname,count(cfc.containerfinishedcheckinfoid) finishedQty
FROM containerfinishedcheckinfo cfc
LEFT JOIN factory f ON f.factoryid = cfc.factoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL ";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cfc.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cfc.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtFinishedQty = dal.GetDataTable(sql);

        //入库记录
        sql = @"
SELECT cis.fevor,COUNT(DISTINCT NVL(cis.charg,cis.aufnr)) instoreQty
FROM containerinstore cis
WHERE 1=1 ";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cis.createdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cis.createdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND cis.fevor = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND cis.fevor IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY cis.fevor";
        DataTable dtInstoreQty = dal.GetDataTable(sql);

        //MES订单建卡
        sql = @"
SELECT f.factoryname,COUNT(DISTINCT m.mfgorderid) containerstartQty
FROM mfgorder m
LEFT JOIN container c ON c.mfgorderid = m.mfgorderid
LEFT JOIN Factory f ON f.factoryid = c.originalfactoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL ";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND c.originalstartdate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND c.originalstartdate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtContainerStartQty = dal.GetDataTable(sql);

        //ERP生产订单
        sql = @"
SELECT f.factoryname,count(DISTINCT mfg.mfgordername) mfgorderQty
FROM mfgorder mfg
LEFT JOIN changestatus cgs ON cgs.parentid = mfg.mfgorderid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL";

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cgs.lastchangedate >= TO_DATE('{0} 00:00:00','YYYY-MM-DD hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cgs.lastchangedate <= TO_DATE('{0} 23:59:59','YYYY-MM-DD hh24:mi:ss') ", arr[1].ToString());
            }
        }
        if (jObject["factoryname"] != null)
        {
            if (jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            else
            {
                sql += " AND ua.attributevalue IN ('601','609','600','607','603')";
            }
        }

        sql += "GROUP BY f.factoryname";
        DataTable dtMfgorderQty = dal.GetDataTable(sql);

        DataTable dtResult = GetAllFactory(jObject);
        dtResult.Columns.Add("MfgorderQty", typeof(Int32));//ERP生产订单
        dtResult.Columns.Add("ContainerStartQty", typeof(Int32));//MES订单建卡
        dtResult.Columns.Add("DispatchQty", typeof(Int32));//派工记录数
        dtResult.Columns.Add("ReportQty", typeof(Int32));//报工记录数
        dtResult.Columns.Add("CheckQty", typeof(Int32));//检验记录数
        dtResult.Columns.Add("InstoreQty", typeof(Int32));//入库记录数

        foreach (DataRow dr in dtResult.Rows)
        {
            string filter = string.Format("factoryname = '{0}'", dr["factoryname"]);
            string fevorfilter = string.Format("fevor = '{0}'", dr["attributevalue"]);
            DataRow[] drMfgorderQty = dtMfgorderQty.Select(filter);
            DataRow[] drContainerStartQty = dtContainerStartQty.Select(filter);
            DataRow[] drDispatchQty = dtDispatchQty.Select(filter);
            DataRow[] drReportQty = dtReportQty.Select(filter);
            DataRow[] drInstoreQty = dtInstoreQty.Select(fevorfilter);
            DataRow[] drSpecCheckQty = dtSpecCheckQty.Select(filter);
            DataRow[] drFinishedQty = dtFinishedQty.Select(filter);

            if (drMfgorderQty.Length > 0)
            {
                dr["MfgorderQty"] = drMfgorderQty[0]["mfgorderQty"];
            }
            else
            {
                dr["MfgorderQty"] = 0;
            }

            if (drContainerStartQty.Length > 0)
            {
                dr["ContainerStartQty"] = drContainerStartQty[0]["containerstartQty"];
            }
            else
            {
                dr["ContainerStartQty"] = 0;
            }

            if (drDispatchQty.Length > 0)
            {
                dr["DispatchQty"] = drDispatchQty[0]["dispatchQty"];
            }
            else
            {
                dr["DispatchQty"] = 0;
            }

            if (drReportQty.Length > 0)
            {
                dr["ReportQty"] = drReportQty[0]["reportQty"];
            }
            else
            {
                dr["ReportQty"] = 0;
            }

            int CheckQty = 0;

            if (drSpecCheckQty.Length > 0)
            {
                CheckQty += Convert.ToInt32(drSpecCheckQty[0]["spcecheckQty"]);
            }

            if (drFinishedQty.Length > 0)
            {
                CheckQty += Convert.ToInt32(drFinishedQty[0]["finishedQty"]);
            }

            dr["CheckQty"] = CheckQty;

            if (drInstoreQty.Length > 0)
            {
                dr["InstoreQty"] = drInstoreQty[0]["instoreQty"];
            }
            else
            {
                dr["InstoreQty"] = 0;
            }
        }
        return dtResult;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        DataTable dtResult = getResultData(jObject);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        DataTable dt = getResultData(jObject);
        dt.Columns.Remove("ATTRIBUTEVALUE");
        dt.Columns["Factoryname"].Caption = "分厂";
        dt.Columns["MfgorderQty"].Caption = "ERP生产订单";
        dt.Columns["ContainerStartQty"].Caption = "MES订单建卡";
        dt.Columns["DispatchQty"].Caption = "派工记录数";
        dt.Columns["ReportQty"].Caption = "报工记录数";
        dt.Columns["CheckQty"].Caption = "检验记录数";
        dt.Columns["InstoreQty"].Caption = "入库数";
        ExportHelper.ExportExcel(dt, "MES系统应用情况月报统计报表");
    }
}